package cn.dmp.report

import java.util.Properties

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.SQLContext

/**
  * Created by Administrator on 2018/4/23.
  */
object RepEquipment {

  def main(args: Array[String]): Unit = {

    if (args.length != 1){
      println(
        """
          |cd.dmp.tool.Biz2Parquet
          |参数不合法，请输入正确参数个数：dataInputPath
        """.stripMargin)
      sys.exit(1)
    }
    val conf = new SparkConf()
      .setMaster("local[*]").setAppName("Biz2Parquet")
      .set("spark.serializer","org.apache.spark.serializer.KryoSerializer")
    val sc = new SparkContext(conf)  //spark离线job的入口

    //输入参数 （模糊匹配）
    val Array(dataInputPath)=args
    //读parquent文件要用SQLContext
    val sqlContext: SQLContext = new SQLContext(sc)
    val dataFrame = sqlContext.read.parquet(dataInputPath)

    //注册临时表
    dataFrame.registerTempTable("parquet_2")

    val sql = sqlContext.sql(
      """
        |select ispname,
        |sum(case when requestmode=1 and processnode>=1 then 1 else 0 end) 总请求,
        |sum(case when requestmode=1 and processnode>=2 then 1 else 0 end) 有效请求,
        |sum(case when requestmode=1 and processnode =3 then 1 else 0 end) 广告请求,
        |sum(case when iseffective=1 and isbilling=1 and isbid=1 and adorderid !=0 then 1 else 0 end) 参与竞价,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) 竞价成功,
        |sum(case when requestmode=2 and iseffective=1 then 1 else 0 end) 展示数,
        |sum(case when requestmode=3 and iseffective=1 then 1 else 0 end) 点击数,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) DSP广告消费,
        |sum(case when iseffective=1 and isbilling=1 and iswin=1 then 1 else 0 end) DSP广告成本
        |from parquet_2
        |group by ispname
      """.stripMargin)

    val url="jdbc:mysql://192.168.20.13:3306/spark?characterEncoding=utf-8"
    val prop = new Properties()
    prop.setProperty("user","root")
    prop.setProperty("password","06141ybw")
    prop.setProperty("driver","com.mysql.jdbc.Driver")
    sql.write.jdbc(url,"isp_rep",prop)

    //关闭资源
    sc.stop()

  }
}
